﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
namespace Billder
{
    public class Catalog
    {
        public static DataTable runSearch(string catalogName, string query)
        {
            DataSet ds = new System.Data.DataSet();
            SqlDataAdapter da = new SqlDataAdapter(
               "EXEC sp_cat_Get" + catalogName + " @PK" + catalogName + "=NULL , @" + catalogName + "='" + query + "'",
                System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static DataTable runSearch(string catalogName, string query , params string[] fields)
        {
            DataSet ds = new System.Data.DataSet();
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append("EXEC sp_cat_Get" + catalogName + " @PK" + catalogName + "=NULL  ");
            foreach( string field in fields ){
                sb.Append(" , @" + field + "='" + query + "'");
            }
            SqlDataAdapter da = new SqlDataAdapter(
               sb.ToString(),
                System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static DataTable get(string catalogName, int pk)
        {
            DataSet ds = new System.Data.DataSet();
            SqlDataAdapter da = new SqlDataAdapter(
               "EXEC sp_cat_Get" + catalogName + " @PK" + catalogName + "='" + pk.ToString() + "'",
                System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static DataTable getSys(string catalogName, int pk)
        {
            DataSet ds = new System.Data.DataSet();
            SqlDataAdapter da = new SqlDataAdapter(
               "EXEC sp_sys_Get" + catalogName + " @PK" + catalogName + "='" + pk.ToString() + "'",
                System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString);
            da.Fill(ds);
            return ds.Tables[0];
        }
        public static void formatResults(DataTable dt, System.Web.HttpResponse Response, params string[] Fields)
        {
            Response.Clear();
            bool first;
            foreach (DataRow r in dt.Rows)
            {
                first = true;
                foreach (string field in Fields)
                {
                    if (!first)
                    {
                        Response.Write("|");
                    }
                    Response.Write(r[field].ToString());
                    first = false;
                }
                Response.Write("\n");
            }
            Response.End();
        }
        public static void formatResultsWithAdd(string searchString,DataTable dt, System.Web.HttpResponse Response, params string[] Fields)
        {
            Response.Clear();
            bool first;
            DataView dv = dt.AsDataView();
            dv.RowFilter = "FKStatus = 3";
            foreach (DataRow r in dv.ToTable().Rows)
            {
                first = true;
                foreach (string field in Fields)
                {
                    if (!first)
                    {
                        Response.Write("|");
                    }
                    Response.Write(r[field].ToString());
                    first = false;
                }
                Response.Write("\n");
            }
            Response.Write("<a href='CreateTicket.aspx?partNumber=" + searchString + "' target='_blank'>Crear ticket para agregar el NP '" + searchString + "'...</a>| | |CreateTicket.aspx?partNumber=" + searchString + "|");
            Response.End();
        }
        public static int add(string catalogName, System.Collections.Hashtable data)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_cat_Add{0} ", catalogName);
                foreach (string parameter in data.Keys)
                {
                    sb.AppendFormat("@{0} = {1} ,", parameter, data[parameter] == null ? "NULL" : "'" + data[parameter] + "'");
                }
                sb.Remove(sb.Length - 1, 1);
                SqlCommand command = new SqlCommand(sb.ToString(), conn);
                command.ExecuteNonQuery();
                sb.Append("; SELECT SCOPE_IDENTITY() AS __latestIdentity;");

                command.CommandText = "SELECT @@IDENTITY AS __latestIdentity;";
                string pkStr = command.ExecuteScalar().ToString();

                return Int32.Parse(pkStr.Equals("") ? "-1" : pkStr);/*
                DataSet ds = new System.Data.DataSet();
                SqlDataAdapter da = new SqlDataAdapter("SELECT SCOPE_IDENTITY() AS __latestIdentity;", conn);
                da.Fill(ds);
                foreach (DataTable dt in ds.Tables)
                {
                    if (dt.Columns[0].ColumnName == "__latestIdentity") {
                        return Int32.Parse(dt.Rows[0]["__latestIdentity"].ToString());
                    }
                }*/
            }
        }
        public static void del(string catalogName, System.Collections.Hashtable data) { 
            del(catalogName, data , false);
        }
        public static void del(string catalogName, System.Collections.Hashtable data , bool sys)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_"+(sys?"sys":"cat")+"_Delete{0} ", catalogName);
                foreach (string parameter in data.Keys)
                {
                    sb.AppendFormat("@{0} = {1} ,", parameter, data[parameter] == null ? "NULL" : "'" + data[parameter] + "'");
                }
                sb.Remove(sb.Length - 1, 1);
                SqlCommand command = new SqlCommand(sb.ToString(), conn);
                command.ExecuteNonQuery();
              
            }
        }
        public static void removeCopy(string type, System.Collections.Hashtable data)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_cat_Del{0}Copy ", type);
                foreach (string parameter in data.Keys)
                {
                    sb.AppendFormat("@{0} = {1} ,", parameter, data[parameter] == null ? "NULL" : "'" + data[parameter] + "'");
                }
                sb.Remove(sb.Length - 1, 1);
                SqlCommand command = new SqlCommand(sb.ToString(), conn);
                command.ExecuteNonQuery();
            }
        }
        public static void reject(string type, System.Collections.Hashtable data)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_cat_Reject{0} ", type);
                foreach (string parameter in data.Keys)
                {
                    sb.AppendFormat("@{0} = {1} ,", parameter, data[parameter] == null ? "NULL" : "'" + data[parameter] + "'");
                }
                sb.Remove(sb.Length - 1, 1);
                SqlCommand command = new SqlCommand(sb.ToString(), conn);
                command.ExecuteNonQuery();
            }
        }
        public static int addSys(string catalogName, System.Collections.Hashtable data)
        {
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_sys_Add{0} ", catalogName);
                foreach (string parameter in data.Keys)
                {
                    sb.AppendFormat("@{0} = {1} ,", parameter, data[parameter] == null ? "NULL" : "'" + data[parameter] + "'");
                }
                sb.Remove(sb.Length - 1, 1);
                SqlCommand command = new SqlCommand(sb.ToString(), conn);
                command.ExecuteNonQuery();

                command.CommandText = "SELECT @@IDENTITY AS __latestIdentity;";
                string pkStr = command.ExecuteScalar().ToString();

                return Int32.Parse(pkStr.Equals("") ? "-1" : pkStr);
            }
            
        }
        public static List<string> partBOMs( string partNumber ) {
            List<string> retVal = new List<string>();
            using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Billder"].ConnectionString))
            {
                conn.Open();
                System.Text.StringBuilder sb = new System.Text.StringBuilder(5000);
                sb.AppendFormat("EXEC sp_sys_PartBOMS '{0}'", partNumber);

                DataSet ds = new System.Data.DataSet();
                SqlDataAdapter da = new SqlDataAdapter(sb.ToString(),conn);
                da.Fill(ds);
                DataTable dt =  ds.Tables[0];
                
                foreach (DataRow row in dt.Rows)
                {
                    retVal.Add(row["BOMName"].ToString());
                }
                
            }
            return retVal;
        }
    }
}